package jdbctemplate;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

@Transactional
public class StudentJdbcTemplate implements IStudentDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;
    private SimpleJdbcCall jdbcCall;
    private PlatformTransactionManager transactionManager;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getCountById");
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void setTransactionManager(PlatformTransactionManager manager) {
        this.transactionManager = manager;
    }

    @Override
    public int createStudent(String name, Integer age) {
        String insertSql = "INSERT INTO student VALUES(NULL, ?, ?)";
        return jdbcTemplate.update(insertSql, name, age);
    }

    @Override
    public int deleteStudent(Integer id) {
        String deleteSql = "DELETE FROM student WHERE ID = ?";
        return jdbcTemplate.update(deleteSql, id);
    }

    @Override
    public int updateStudent(Integer id, String name, Integer age) {
        String updateSql = "UPDATE student SET name = ? ,age = ? WHERE id = ?";
        return jdbcTemplate.update(updateSql, name, age, id);
    }

    @Override
    public Student getStudent(Integer id) {
        String querySql = "SELECT * FROM student WHERE id = ?";
        Student student = jdbcTemplate.queryForObject(querySql, new Object[]{id}, new StudentMapper());
        return student;
    }

    @Override
    public List<Student> getStudentList() {
        String querySql = "SELECT * FROM student";
        return jdbcTemplate.query(querySql, new StudentMapper());
    }

    public void getCountById(int id) {
        SqlParameterSource in = new MapSqlParameterSource().addValue("iId", id);
        Map<String, Object> mapOut = jdbcCall.execute(in);
        System.out.println((int)mapOut.get("c") + "");
    }

    public void insertNewStudentWithTransactionManager() {
        TransactionDefinition def = new DefaultTransactionDefinition();
        TransactionStatus status = this.transactionManager.getTransaction(def);

        try {
            createStudent("小赵1", 30);
            //int i = 1 / 0;

            transactionManager.commit(status);
        } catch (Exception ex) {
            transactionManager.rollback(status);
        }

        List<Student> students = getStudentList();
        for (Student student1: students) {
            System.out.println(student1.toString());
        }
    }

    /**
     * 使用声明式的方式进行事务的管理
     */
    public void insertNewStudentWithAop() {
        TransactionDefinition def = new DefaultTransactionDefinition();
        TransactionStatus status = this.transactionManager.getTransaction(def);
        try {
            createStudent("小马1", 30);
            int a = 1/0;

            transactionManager.commit(status);
        } catch (Exception ex) {
            transactionManager.rollback(status);
            System.out.println("ex...");
        }

        List<Student> students = getStudentList();
        for (Student student1: students) {
            System.out.println(student1.toString());
        }
    }
}
